<?php 
App::uses('Folder', 'Utility'); 

class SqlShell extends Shell { 
    private $version; 
    private $versionFile; 
     
    function startup() { 
        parent::startup(); 
        $this->out('Cake Sql Shell'); 
        $this->hr(); 
        $this->versionFile = APP.'config/sql/version.txt'; 
        if (!is_file($this->versionFile)) { 
            $folder = new Folder(dirname($this->versionFile),true); 
            if (file_put_contents($this->versionFile, "0")) { 
                $this->out("Created version File with initial version 0 in ".$this->versionFile);     
                $this->hr(); 
            } else { 
                $this->out("Couldn't create version File in ".$this->versionFile.", aborting."); 
                $this->_stop(); 
            } 
        } 
         
    } 

    private function getVersion() { 
        $this->version = trim(file_get_contents($this->versionFile)); 
        if (!is_numeric($this->version)) { 
            $this->out('Version not numeric in APP/config/sql/version.txt File, aborting...'); 
            $this->_stop(); 
        } 
        return (int)$this->version; 
    }  
     
    private function setVersion($version) { 
        if (!file_put_contents($this->versionFile, $version)) { 
            $this->out("Couldn't update version.txt file, aborting..."); 
            $this->_stop(); 
        } 
    } 

    function main() { 
        $this->out('Currently at version '.$this->getVersion()); 
    } 

    private function getSql($number) { 
        if (($text = file_get_contents($filename = APP.'config/sql/'.$number.'.sql')) !== false) { 
            return $text; 
        } else { 
            $this->out("Couldn't load contents of file {$filename}, aborting..."); 
            $this->_stop(); 
        } 
    } 

    function backup($version) { 
        $folder = new Folder(TMP . 'dumps', true); 
        $c = ConnectionManager::getInstance()->config->default; 
        $this->filename = TMP . 'dumps'.DS.'sql_shell_backup_'.$version.'.sql.gz'; 
        $this->out("Writing backup dump file to $this->filename"); 
        if (file_exists($this->filename)) { 
            if ($this->in('File exists, overwrite? [y/n]') !== 'y') { 
                return; 
            } 
        } 
        $command = exec($c = "mysqldump -u {$c['login']} --password={$c['password']} -h {$c['host']} {$c['database']} | gzip > $this->filename"); 
        if (!file_exists($this->filename)) { 
            $this->out("Couldn't create backup, aborting."); 
            $this->_stop(); 
        } 
    } 

    function update() { 
        $sqlFolder = new Folder(APP.'config/sql'); 
        list($dirs, $files)     = $sqlFolder->read(); 
        foreach ($files as $i => $file) { 
            if (!preg_match( '/\d+\.sql$/', $file))  { 
                unset($files[$i]); 
            } 
        } 
        sort($files, SORT_NUMERIC); 
        foreach ($files as $i => $file) { 
            $fileParts = explode(".", $file); 
            if ($fileParts[0] !== (string)($i+1)) { 
                return $this->out("Can't run update, something broken with sql files"); 
            } 
        } 
        if (empty($this->args)) { 
            $version = count($files);  
        } else { 
            if (!is_numeric($this->args[0])) { 
                $this->out('Version number must be numeric'); 
                $this->_stop(); 
            } 
            if (!isset($files[$this->args[0]-1])) { 
                $this->out('Version doesn\'t exist: '.$this->args[0]); 
                $this->_stop(); 
            } 
            $version = $this->args[0]; 
        } 
        App::import('Model', 'AppModel'); 
         
        $model = new AppModel(false, false); 
        for ($currentVersion = $this->getVersion(); $currentVersion < $version; $currentVersion = $this->getVersion()) { 
            $this->out('Currently at Version '.$currentVersion); 
            $this->out('Updating to Version '.($currentVersion+1).', executing sql:'); 
            $this->hr(); 
            $this->out($sql = $this->getSql($currentVersion+1)); 
            $this->hr(); 
            if ($this->in('Execute SQL? [y/n]') !== 'y') { 
                break; 
            } else { 
                $this->backup($currentVersion); 
                $sql = explode("\n\n", $sql); 
                foreach ($sql as $s) 
                    if (trim($s)) { 
                        $model->query($s);     
                    } 
                $this->setVersion((int)($currentVersion+1));     
            } 
        } 
        $this->out('Now at version '.$this->getVersion()); 
    } 
} 
?> 